package poi.copypic1;

import java.awt.FontMetrics;
import java.awt.Graphics;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * Hello world!
 *
 */
public class App {
	public static void main(String[] args) {
		/*addPicToExcel("d:/pic/clock.jpg", "D:/pic/pic.xls","Sheet2", (short) 1, 1, (short) 4, 4);
		addPicToExcel("d:/pic/clock.jpg", "D:/pic/pic.xls","Sheet2", (short) 1, 6, (short) 4, 10);
		addPicToExcel("d:/pic/clock.jpg", "D:/pic/pic.xls","Sheet1", (short) 1, 6, (short) 4, 9);*/
		
		/*addPicToExcel("d:/pic/calculatePayment.jpg", "D:/pic/pic.xls","Sheet2", (short) 1, 1, (short) 4, 4);
		addPicToExcel("d:/pic/calculatePayment.jpg", "D:/pic/pic.xls","Sheet2", (short) 1, 6, (short) 4, 10);
		addPicToExcel("d:/pic/calculatePayment.jpg", "D:/pic/pic.xls","Sheet1", (short) 1, 6, (short) 4, 9);*/
		
		/*addPicToExcel("d:/pic/kk-immersive-n5.jpg", "D:/pic/pic.xls","Sheet2", (short) 1, 1, (short) 4, 4);
		addPicToExcel("d:/pic/kk-immersive-n5.jpg", "D:/pic/pic.xls","Sheet2", (short) 1, 6, (short) 7, 64);
		addPicToExcel("d:/pic/kk-immersive-n5.jpg", "D:/pic/pic.xls","Sheet1", (short) 1, 6, (short) 4, 9);*/
		
		/*addPicToExcel("d:/pic/play_dev_guide_b.jpg", "D:/pic/pic.xls","Sheet2", (short) 1, 1, (short) 4, 4);
		addPicToExcel("d:/pic/play_dev_guide_b.jpg", "D:/pic/pic.xls","Sheet2", (short) 1, 6, (short) 7, 64);
		addPicToExcel("d:/pic/play_dev_guide_b.jpg", "D:/pic/pic.xls","Sheet1", (short) 1, 6, (short) 4, 9);*/
		
		addPicToExcel("d:/pic/clock.jpg", "D:/pic/pic.xls","Sheet2", (short) 1, 1, (short) 4, 4);
		addPicToExcel("d:/pic/clock.jpg", "D:/pic/pic.xls","Sheet2", (short) 1, 6, (short) 7, 64);
		addPicToExcel("d:/pic/clock.jpg", "D:/pic/pic.xls","Sheet1", (short) 1, 6, (short) 4, 9);
	}

	/**
	 * 将图片加入到excel的指定位置
	 * 
	 * @param srcPicPath
	 *            图片路径
	 * @param desExcelPath
	 *            excel路径
	 * @param sheetName
	 * 			     加入图片的sheet名         
	 * @param firstCellCol
	 *            图片起始excel格的列坐标
	 * @param firstCellRow
	 *            图片起始excel格的行坐标
	 * @param endCellCol
	 *            图片结束excel格的列坐标
	 * @param endCellRow
	 *            图片结束excel格的行坐标
	 */
	public static boolean addPicToExcel(String srcPicPath, String desExcelPath, String sheetName, short firstCellCol, int firstCellRow,
			short endCellCol, int endCellRow) {
		FileOutputStream fileOut = null;
		BufferedImage bufferImg = null;
		InputStream inp = null;
		// 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
		try {
			ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
			bufferImg = ImageIO.read(new File(srcPicPath));
			ImageIO.write(bufferImg, "jpg", byteArrayOut);

			int picHeight = bufferImg.getHeight();
			int picWidth = bufferImg.getWidth();
			
			System.out.println("height:"+picHeight+",width:"+picWidth);
			
			inp = new FileInputStream(desExcelPath);
			//HSSFWorkbook wb = new HSSFWorkbook();
			Workbook wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheet(sheetName);
			if(sheet == null) {
				return false;
			}
			//Row row = sheet1.getRow(firstCellRow);
			//Cell cell = row.getCell(firstCellCol);
			
			//CellStyle cellStyle = cell.getCellStyle();
			/*Font font = wb.getFontAt(cellStyle.getFontIndex());
			Graphics graphics =new java.awt.image.BufferedImage (10,10,java.awt.image.BufferedImage.TYPE_INT_ARGB ).getGraphics(); 
			java.awt.Font stringFont = new java.awt.Font(font.getFontName(),java.awt.Font.PLAIN, font.getFontHeightInPoints());
			FontMetrics fontMetrics = graphics.getFontMetrics(stringFont);*/
			
			/*double cellWidth = (sheet1.getColumnWidth(cell.getColumnIndex()) * (8-1) + 5) / (8-1) *256;
			double widthScale = cellWidth * (endCellCol - firstCellCol + 1) / picWidth;*/
			
			// 计算单元格的长宽
			//double cellWidth = sheet1.getColumnWidthInPixels(cell.getColumnIndex());
			//double cellHeight = cell.getRow().getHeightInPoints()/72*96;
			
			double cellWidth = getCellWidth(sheet, firstCellCol, firstCellRow, endCellCol, endCellRow);
			double cellHeight = getCellHeight(sheet, firstCellCol, firstCellRow, endCellCol, endCellRow);
			double widthScale = cellWidth / picWidth;
			double heightScale = cellHeight/picHeight;
			
			System.out.println("cellHeight:"+cellHeight+"---cellWidth:"+cellWidth);
			System.out.println("heightScale:"+heightScale+"---widthScale:"+widthScale);
			
			double scale = 0;
			if(widthScale < heightScale) {
				scale = widthScale;
			} else {
				scale = heightScale;
			}
			// 画图的顶级管理器，一个sheet只能获取一个
			Drawing patriarch = sheet.createDrawingPatriarch();
			// anchor主要用于设置图片的属性
			HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, firstCellCol, firstCellRow, endCellCol, endCellRow);
			anchor.setAnchorType(3);
			// 插入图片
			Picture pict = patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
			BigDecimal bigScale = new BigDecimal(scale);
			if(bigScale.compareTo(BigDecimal.ONE) >=0 ) {
				pict.resize(1.0d);
			} else {
				pict.resize(scale);
			}
			
			fileOut = new FileOutputStream(desExcelPath);
			// 写入excel文件
			wb.write(fileOut);
			System.out.println("----Excle文件已生成------");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (fileOut != null) {
				try {
					fileOut.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if (inp != null) {
				try {
					inp.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return true;
	}
	
	/**
	 * 计算预备的excel中图片区域的总宽度
	 * @param sheet 加入图片的sheet
	 * @param firstCellCol 图片起始excel格的列坐标
	 * @param firstCellRow 图片起始excel格的行坐标
	 * @param endCellCol 图片结束excel格的列坐标
	 * @param endCellRow 图片结束excel格的行坐标
	 * @return 预备的excel中图片区域的总宽度
	 */
	public static double getCellWidth(Sheet sheet, short firstCellCol, int firstCellRow,
			short endCellCol, int endCellRow) {
		double sumCellWidth = 0d;
		double cellWidth = 0d;
		
		if(sheet == null) {
			return 0d;
		}
		
		for(int i = firstCellCol; i <= endCellCol; i++) {
			Row row = sheet.getRow(firstCellRow);
			
			if(row == null) {
				return 0d;
			}
			
			Cell cell = row.getCell(i);
			
			if(cell == null) {
				return 0d;
			}
			
			/*cellWidth = sheet.getColumnWidth(cell.getColumnIndex());
			sumCellWidth = sumCellWidth + (cellWidth * (8-1) + 5) / (8-1) *256;*/
			cellWidth = getColumnWidthInPixels(sheet, cell.getColumnIndex());
			sumCellWidth = sumCellWidth + cellWidth;
		}
		return sumCellWidth;
	}
	
	/**
	 * 计算预备的excel中图片区域的总高度
	 * @param sheet 加入图片的sheet
	 * @param firstCellCol 图片起始excel格的列坐标
	 * @param firstCellRow 图片起始excel格的行坐标
	 * @param endCellCol 图片结束excel格的列坐标
	 * @param endCellRow 图片结束excel格的行坐标
	 * @return 预备的excel中图片区域的总高度
	 */
	public static double getCellHeight(Sheet sheet, short firstCellCol, int firstCellRow,
			short endCellCol, int endCellRow) {
		float sumCellHeight = 0f;
		float cellHeight = 0f;
		double retSumCellHeight = 0d;
		
		if(sheet == null) {
			return 0d;
		}
		
		for(int i = firstCellRow; i <= endCellRow; i++) {
			Row row = sheet.getRow(i);
			
			if(row == null) {
				return 0d;
			}
			
			cellHeight = row.getHeightInPoints();
			sumCellHeight = sumCellHeight + cellHeight;
		}
		retSumCellHeight = sumCellHeight/72*96;
		return retSumCellHeight;
	}
	
	public static float getColumnWidthInPixels(Sheet sheet, int column){
        int cw = sheet.getColumnWidth(column);
        int def = sheet.getDefaultColumnWidth()*256;
        float px = (cw == def ? PX_DEFAULT : PX_MODIFIED);

        return cw/px;
    }
	
	/**
     * width of 1px in columns with default width in units of 1/256 of a character width
     */
    private static final float PX_DEFAULT = 32.00f;
    /**
     * width of 1px in columns with overridden width in units of 1/256 of a character width
     */
    private static final float PX_MODIFIED = 36.56f;

	//图片自适应
	//1.图片宽高都比cell小
	//2.图片宽高都比cell大
	//3.图片宽比cell小
	//4.图片高比cell小
	//
	//
}